package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Hashtable;
import java.util.Vector;

import entidades.Grupo;

public class GruposDAO {

	public static void cargarGrupos(Vector<Grupo> listaGrupos) throws SQLException {
		if (listaGrupos.size() != 0) {
			Connection conexion = PrincipalDAO.connect();
		
			String sql ="INSERT INTO `grupos` ( " +
						"	`id` , `nombreGrupo` " +
						" ) " +
						" VALUES ( ? , ? );";
			
			PreparedStatement prep = conexion.prepareStatement(sql);
			
			for (int i=0; i < listaGrupos.size(); i++) {
				prep.setInt(1, listaGrupos.elementAt(i).getId());
				prep.setString(2, listaGrupos.elementAt(i).getNombre());
				prep.addBatch();
			}
			
			conexion.setAutoCommit(false);
			prep.executeBatch();
			conexion.setAutoCommit(true);
			conexion.close();
		}		
	}
	
	
	public static void crearTablaGrupos() throws SQLException {
		
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();
		
		
		statement.execute("DROP TABLE IF EXISTS `grupos`");
		String sql =" CREATE TABLE grupos ( " +
					" `id` INT NOT NULL , " +
					" `nombreGrupo` VARCHAR( 100 ) NOT NULL , PRIMARY KEY ( `id` ) ,UNIQUE (`nombreGrupo`) " +
					" )  ; ";
		
		//System.out.println(sql);
		statement.executeUpdate(sql);
		conexion.close();
	
	}


	public static int getIdUltimoGrupo() throws SQLException {
		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();
		
		String sql =" SELECT MAX(`id`) AS idFinal " +
					" FROM grupos ";
		
		//System.out.println(sql);
		ResultSet rs = statement.executeQuery(sql);
		Integer maxId;
		
		if (rs.next()) {
			maxId = rs.getInt("idFinal");
			System.out.println("Maximo id grupo : " + maxId);
		}
		else 
			maxId = 0;			
		
		conexion.close();
		rs.close();
		return maxId;
	}


	public static Hashtable<String, Integer> gruposYaCargados() throws SQLException {
		Hashtable<String, Integer> idGrupo = new Hashtable<String, Integer>();

		Connection conexion = PrincipalDAO.connect();
		Statement statement = conexion.createStatement();
		
		String sql =" SELECT * " +
					" FROM grupos ";
		
		//System.out.println(sql);
		ResultSet rs = statement.executeQuery(sql);
		Integer id;
		String nombre;
		
		while (rs.next()) {
			id = rs.getInt("id");
			nombre = rs.getString("nombreGrupo");
			//System.out.println(nombre);
			idGrupo.put(nombre, id);
		}
		
		conexion.close();
		rs.close();
		
		return idGrupo;
	}

}
